package com.pub.dbtools;

import org.jeecgframework.core.util.PropertiesUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.RandomAccessFile;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
public class DBOperator {

	public static synchronized  final Connection getConnect(String dbname) {
		try {
			PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

			String driver = tmpvo.readProperty( "jdbc.driver");
			String url = tmpvo.readProperty( "jdbc.url");
			String user = tmpvo.readProperty( "jdbc.username");
			String password = tmpvo.readProperty( "jdbc.password");
			if (dbname.equals("mysqltx")){
				url = tmpvo.readProperty( "jdbc.url.mysqltx");
			}
				Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
				Properties info = new Properties();
				info.setProperty("proxool.maximum-connection-count", "100");
				info.setProperty("proxool.house-keeping-test-sql",
						"select now()");
				info.setProperty("user", user);
				info.setProperty("password", password);
				url += "?relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8";

				Connection con = DriverManager.getConnection("proxool.example:"
						+ driver + ":" + url, info);


	/*		Class.forName("com.mysql.jdbc.Driver");
			Connection con = java.sql.DriverManager.getConnection(url,
					user, password);*/

				System.out.println(con);
				return con;

		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}

	public static final String execDML(String dbname, String strSql) {

		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create") || strTemp.startsWith("drop")
					|| strTemp.startsWith("alter")
					|| strTemp.startsWith("delete")
					|| strTemp.startsWith("insert")
					|| strTemp.startsWith("comment")
					|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}

	public static final String execDML(String strSql) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create") || strTemp.startsWith("drop")
					|| strTemp.startsWith("alter")
					|| strTemp.startsWith("delete")
					|| strTemp.startsWith("insert")
					|| strTemp.startsWith("comment")
					|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}

	public static final String execSql(ArrayList sqls) {

		if (sqls.size() < 1) {
			return "ok";
		}

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		String debugsign = tmpvo.readProperty("debugsign");
		Statement stmtDB = null;



		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
			    //if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
			    //}
				strSql = ((String) sqls.get(i)).trim();

				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	public static final String execSql(String str) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			//if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			//}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	public static final String execSqlComple(String str) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;
		Connection con = getConnect(dbname);
		int results = 0;

		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			//if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			//}
			results = stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			if (results == 0) {
				return "zero";
			} else {
				return "ok";
			}
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	public static final String execSql(String dbname, String str) {
		Statement stmtDB = null;
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String debugsign = tmpvo.readProperty("debugsign");
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			//if (debugsign.toLowerCase().equals("yes")) {
				System.err.println(str);
			//}
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	public static final String execSql(String dbname, ArrayList sqls) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String debugsign = tmpvo.readProperty("debugsign");

		Statement stmtDB = null;

		if (sqls.size() < 1) {
			return "ok";
		}
		Connection con = getConnect(dbname);
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				//if (debugsign.toLowerCase().equals("yes")) {
					System.err.println((String) sqls.get(i));
				//}
				strSql = (String) sqls.get(i);
				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	public static int countNum(String fromSql, String searWhere,
			String searOrder, String addsql, String groupsql) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currdb = tmpvo.readProperty("jdbc.dbType");


		return countNum(fromSql, searWhere, searOrder, addsql, groupsql, currdb);
	}

	public static int countNum(String fromSql, String searWhere,
			String searOrder, String addsql, String groupsql, String dbmode) {
		String counRealSql = addsql + " select count(*) as num " + fromSql
				+ "  " + searWhere + " " + groupsql;
		DataSimpDto pubTemp = new DataSimpDto(dbmode, counRealSql, -1);
		if (pubTemp.getTotRecord() < 1)
			return 0;
		if (pubTemp.getDataValue("num", 0).equals(""))
			return 0;
		if (pubTemp.getDataValue("num", 0).equals("0"))
			return 0;

		return Integer.parseInt(pubTemp.getDataValue("num", 0));
	}

	public static final String initQuerySQL(String currdb, String sql,
			String fromsql, String wheresql, String orderSql, int startIndex,
			int pageSize, String groupsql, String addsql) {

		if (currdb.startsWith("oracle")) {
			StringBuffer querySQL = new StringBuffer();
			int totrow = pageSize + startIndex;
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(addsql + " " + sql + " " + fromsql + " " + wheresql
							+ " " + groupsql + " " + orderSql)
					.append(") my_table ").append(") where my_rownum>")
					.append(startIndex + " and my_rownum<=" + totrow);

			return querySQL.toString();
		}
		if (currdb.startsWith("mysql")) {
			StringBuffer querySQL = new StringBuffer();
			int totrow = pageSize + startIndex;
			querySQL.append(addsql + " " + sql + " " + fromsql + " " + wheresql
					+ " " + groupsql + " " + orderSql + " limit " + startIndex
					+ "," + pageSize);

			return querySQL.toString();

		}
		if (currdb.startsWith("db2")) {
			StringBuffer querySQL = new StringBuffer();
			int totrow = pageSize + startIndex;

			sql = sql + ",rownumber() over(" + orderSql + ") as rowid ";
			sql += fromsql + " " + wheresql + " " + groupsql;

			sql = "select * from (" + sql + ") as a where a.rowid > "
					+ startIndex + " AND a.rowid <=" + totrow;

			return sql;
		}
		return "";
	}

	public static final String initQuerySQL(String sql, String fromsql,
			String wheresql, String orderSql, int startIndex, int pageSize,
			String groupsql, String addsql) {

 			StringBuffer querySQL = new StringBuffer();
			int totrow = pageSize + startIndex;
			querySQL.append(addsql + " " + sql + " " + fromsql + " " + wheresql
					+ " " + groupsql + " " + orderSql + " limit " + startIndex
					+ "," + pageSize);

			return querySQL.toString();


	}

	public static final String execSqlDb(String str,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(str);
	}

	public static final String execSqlDb(ArrayList tmparr,
			javax.servlet.http.HttpServletRequest request) {
		return execSql(tmparr);
	}

	public static final String execProc(String str, ArrayList tmparr) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;

		if (con == null)
			return "error";
		try {
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.execute();

			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				cstmt.close();
				con.close();
				con = null;
				return String.valueOf(se.toString() + " ���ò���-"
						+ tmparr.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	public static final ArrayList execProc(String str, ArrayList tmparr,
			int nretnum) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");
		String debugsign = tmpvo.readProperty( "debugsign");
		if (debugsign.equalsIgnoreCase("yes")) {
			System.out.println("执行存储过程 " + str + ":" + tmparr.toString());
		}
		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			for (int s = 0; s < nretnum; s++) {
				str += "?,";
			}

			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			for (int s = 0; s < nretnum; s++) {
				proc.registerOutParameter(s + ntotonum, Types.VARCHAR);
			}
			proc.execute();
			for (int s = 0; s < nretnum; s++) {
				String retvalue = proc.getString(s + ntotonum);
				tmparrret.add(retvalue);
			}

			con.close();
			con = null;
			if (debugsign.equalsIgnoreCase("yes")) {
				System.out.println("ִ执行存储过程" + str + "  返回结果:"
						+ tmparrret.toString());
			}
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				cstmt.close();
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	public static final ArrayList execProcSet(String str, ArrayList tmparr,
			int nretnum) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		Connection con = getConnect(dbname);
		CallableStatement cstmt = null;
		ResultSet rs = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			str += "?";
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.registerOutParameter(ntotonum, oracle.jdbc.OracleTypes.CURSOR);
			proc.execute();
			rs = (ResultSet) proc.getObject(ntotonum);

			while (rs.next()) {
				String[] tmpstr = new String[nretnum];
				for (int s = 0; s < nretnum; s++) {
					tmpstr[s] = rs.getString(s + 1);
				}
				tmparrret.add(tmpstr);
			}
			if (rs != null) {
				rs.close();
			}
			con.close();
			con = null;
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				rs.close();
				cstmt.close();
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}

	public static final String updateBlob(String filename, String wheresql,
			String tablename, String fieldname) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname = tmpvo.readProperty("jdbc.dbType");

		String updsql = "update " + tablename + " set " + fieldname
				+ "=? where " + wheresql;
		File files = new File(filename);
		long l1 = files.length();
		String ret = "error";
		int l2 = (int) l1;
		try {
			FileInputStream fis = new FileInputStream(files);
			Connection con = getConnect(dbname);
			PreparedStatement ps = con.prepareStatement(updsql);

			ps.setBinaryStream(1, fis, l2);
			ps.executeUpdate();
			ps.close();
			fis.close();
			con.close();
			return "ok";
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ret;
	}

	public static final String readBlob(String filename, String wheresql,
			String tablename, String fieldname) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String dbname = tmpvo.readProperty("jdbc.dbType");

		String selesql = "select " + fieldname + " from " + tablename
				+ " where " + wheresql;

		File files = new File(filename);
		long l1 = files.length();
		String ret = "error";
		int l2 = (int) l1;
		try {
			Connection con = getConnect(dbname);
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(selesql);
			while (rs.next()) {

				Blob blob = (Blob) rs.getBlob(1);
				java.io.InputStream inputStream = blob.getBinaryStream();

				RandomAccessFile rf2 = new RandomAccessFile(filename, "rw");
				rf2.writeBytes("");
				rf2.close();
				File fileOutput = new File(filename);
				FileOutputStream fo = new FileOutputStream(fileOutput);
				int c;
				while ((c = inputStream.read()) != -1)
					fo.write(c);
				fo.close();
			}
			return "ok";
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ret;
	}
}
